package simpleDB;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DatabaseHelper extends SQLiteOpenHelper {
	static final String dbName = "simpleDB2";
	static final String repCounterTable = "repcounter";
	static final String repC_id = "id";
	static final String repC_persId = "persId";
	static final String repC_date = "date";
	static final String repC_exerciseId = "exerciseId";
	static final String repC_repetitions = "repetitions";
	static final String repC_accuracy = "accuracy";
	static final String repC_transmitted = "trasmitted";
	static final String repC_duration = "duration";
	static final String[] repC__columns = new String[] { repC_id, repC_persId,
			repC_date, repC_exerciseId, repC_repetitions, repC_accuracy,
			repC_transmitted, repC_duration };
	// Persons Table
	static final String personTable = "person";
	static final String person_id = "id";
	static final String person_weigth = "weigth";
	static final String person_name = "name";
	static final String person_height = "height";
	static final String person_birthdate = "birthdate";
	static final String person_level = "level";
	static final String person_googleToken = "googletoken";
	static final String person_transmitted = "trasmitted";
	

	static final int maxExerciseId_moveItElsewhere = 5;

	SQLiteDatabase db;

	public String getStartEndOFWeek(int enterWeek, int enterYear) {
		Calendar calendar = Calendar.getInstance();
		calendar.clear();
		calendar.set(Calendar.WEEK_OF_YEAR, enterWeek);
		calendar.set(Calendar.YEAR, enterYear);

		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		Date startDate = calendar.getTime();
		String startDateInStr = formatter.format(startDate);
		// System.out.println("...date..."+startDateInStr);

		calendar.add(Calendar.DATE, 6);
		Date enddate = calendar.getTime();
		String endDaString = formatter.format(enddate);
		// System.out.println("...date..."+endDaString);

		return startDateInStr + " bis " + endDaString;
	}

	public DatabaseHelper(Context context) {

		super(context, dbName, null, 1);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// dropTables();
		// createTables();

	}

	
	
	public void fillDBForFirstUsage(Context cx) {
		SQLiteDatabase db = this.getReadableDatabase();
		String sqlStr = "SELECT name FROM sqlite_master WHERE type='table' AND name='repcounter';";
		Cursor c = db.rawQuery(sqlStr, null);
		Log.i("main", "records: " + c.getCount());
		if (c.getCount() == 0) {
			Toast.makeText(cx, "Creating Database...",
					Toast.LENGTH_SHORT).show();
			this.dropTables();
			this.createTables();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + repCounterTable);
	}

	public void createTables() {
		SQLiteDatabase db = this.getWritableDatabase();
		String sqlStr = "CREATE TABLE " + repCounterTable + " (" + repC_id
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + repC_persId
				+ " INTEGER, " + repC_date + " TEXT, " + repC_exerciseId
				+ " INTEGER, " + repC_repetitions + " INTEGER, "
				+ repC_accuracy + " INTEGER, " + repC_transmitted
				+ " INTEGER, " + repC_duration + " INTEGER )";
		Log.i("DB", sqlStr);
		db.execSQL(sqlStr);

		sqlStr = "CREATE TABLE " + personTable + " (" + person_id
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + person_googleToken
				+ " TEXT, " + person_name + " TEXT, " + person_weigth
				+ " INTEGER, " + person_transmitted + " INTEGER, " + person_birthdate
				+ " TEXT, " + person_height + " INTEGER, "
				+ person_level + " INTEGER)";
		Log.i("DB", sqlStr);
		db.execSQL(sqlStr);
	}

	public void dropTables() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.execSQL("drop table if exists repcounter");
		db.execSQL("drop table if exists person");
		db.close();
	}

	public void writeTestData(int persId, String date, int exerciseId,
			int repetitions) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(repC_persId, persId);
		cv.put(repC_date, date);
		cv.put(repC_exerciseId, exerciseId);
		cv.put(repC_repetitions, repetitions); 

		db.insert(repCounterTable, repC_repetitions, cv);

		db.close();
	}

	public void readRepTable() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery("select * from repcounter", null);
		while (c.moveToNext()) {
			Log.i("GYRO", "id: " + c.getInt(c.getColumnIndex(repC_id)));
			Log.i("GYRO",
					"ex_id: " + c.getInt(c.getColumnIndex(repC_exerciseId)));
			Log.i("GYRO", "date: " + c.getString(c.getColumnIndex(repC_date)));
			Log.i("GYRO", "pers_id: " + c.getInt(c.getColumnIndex(repC_persId)));
			Log.i("GYRO",
					"reps: " + c.getInt(c.getColumnIndex(repC_repetitions)));
		}
	}

	/**
	 * 
	 * @param persId
	 * @param date
	 * @param exerciseId
	 * @param repetitions
	 * @return Returns true if a new record was inserted, false otherwise
	 */
	public boolean addRepetitions(int persId, String date, int exerciseId,
			int repetitions, int accuracy, int duration) {
		SQLiteDatabase db = this.getWritableDatabase();
		//int dur = (int) (duration / 1000000);
		Log.i("mala", "durationssssss: " + duration);
		Cursor c = db.query(
				repCounterTable,
				repC__columns,
				repC_persId + "=? AND " + repC_date + "=? AND "
						+ repC_exerciseId + "=?",
				new String[] { String.valueOf(persId), date,
						String.valueOf(exerciseId) }, null, null, null);

		if (c.getCount() == 0) { // insert new record

			ContentValues cv = new ContentValues();
			cv.put(repC_persId, persId);
			cv.put(repC_date, date);
			cv.put(repC_exerciseId, exerciseId);
			cv.put(repC_repetitions, repetitions);
			cv.put(repC_accuracy, accuracy);
			cv.put(repC_duration, duration);
			db.insert(repCounterTable, repC_repetitions, cv);
			db.close();
			return true;

		} else { // update record
			c.moveToFirst();
			int affectedId = c.getInt(c.getColumnIndex(repC_id));
			int repsBefore = c.getInt(c.getColumnIndex(repC_repetitions));
			int accuracyBefore = c.getInt(c.getColumnIndex(repC_accuracy));
			int durationBefore = c.getInt(c.getColumnIndex(repC_duration));

			String strFilter = repC_id + "=" + affectedId;
			ContentValues args = new ContentValues();

			args.put(repC_repetitions, repsBefore + repetitions);
			args.put(repC_accuracy, accuracyBefore + accuracy);
			args.put(repC_duration, durationBefore + duration);
			
			db.update(repCounterTable, args, strFilter, null);
			db.close();
			return false;
		}
	}

	public void addPerson(PersonDataObject person) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(personTable, person_id + "=" + person.id, null);
		ContentValues cv = new ContentValues();
		cv.put(person_birthdate, person.birthdate);
		cv.put(person_id, person.id);
		cv.put(person_weigth, person.weight);
		cv.put(person_name, person.name);
		cv.put(person_birthdate, person.birthdate);
		cv.put(person_level, person.level);
		cv.put(person_googleToken, person.googletoken);
		cv.put(person_transmitted, person.transmitted);
		cv.put(person_height, person.height);
		db.insert(personTable, null, cv);
		db.close();		
	}
	
	// public void insertNewRecord(String teststr, int repetitions) {
	// SQLiteDatabase db = this.getWritableDatabase();
	// ContentValues cv = new ContentValues();
	// cv.put (colTestStr, "martin");
	// cv.put (colRepetitions, 17);
	// db.insert(repCounterTable, colRepetitions, cv);
	// db.close();
	// }
	//

	public StatisticDataObject getRepetitions(int persId, String date,
			int exerciseId) {
		StatisticDataObject sdo = new StatisticDataObject();
		SQLiteDatabase db = this.getReadableDatabase();

		// String [] columns=new String[]{colID, colPersId, colDate,
		// colExerciseId, colRepetitions};

		Cursor c = db.query(
				repCounterTable,
				repC__columns,
				repC_persId + "=? AND " + repC_date + "=? AND "
						+ repC_exerciseId + "=?",
				new String[] { String.valueOf(persId), date,
						String.valueOf(exerciseId) }, null, null, null);
		int rep = 0;
		if (c.getCount() != 0) {
			c.moveToFirst();
			sdo.repetitions = c.getInt(c.getColumnIndex(repC_repetitions));
			sdo.duration = c.getInt(c.getColumnIndex(repC_duration));
			sdo.accuracy = c.getInt(c.getColumnIndex(repC_accuracy));
		}
		db.close();
		return sdo;
	}

	public ArrayList<StatisticDataObject> getRepetionsPerMonth(int persId,
			int year, int exId) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery("select * from repcounter where " + repC_persId
				+ " = " + persId + " AND " + repC_date + ">='" + year
				+ "-00-00' AND " + repC_date + "<='" + year + "-99-99' AND "
				+ repC_exerciseId + "=" + exId, null);

		// ArrayList<StatisticDataObject> al = new
		// ArrayList<StatisticDataObject>(Collections.nCopies(12, sdo_00));
		ArrayList<StatisticDataObject> al = new ArrayList<StatisticDataObject>(12);

		for (int i = 0; i < 13; i++) {
			StatisticDataObject sdo_00 = new StatisticDataObject();
			al.add(i, sdo_00);
		}

		while (c.moveToNext()) {
			String date = c.getString(c.getColumnIndex(repC_date));
			int month = Integer.valueOf(date.substring(5, 7));
			StatisticDataObject sdo = al.get(month);

			sdo.repetitions += c.getInt(c.getColumnIndex(repC_repetitions));
			sdo.duration += c.getInt(c.getColumnIndex(repC_duration));
			sdo.accuracy += c.getInt(c.getColumnIndex(repC_accuracy));

			al.set(month, sdo);
		}

		return al;

	}

	public PersonDataObject getPerson(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery("select * from person where " + person_id + " = " + id, null);
		c.moveToFirst();
		PersonDataObject pdo = new PersonDataObject();
		pdo.id = id;
		pdo.birthdate = c.getString(c.getColumnIndex(person_birthdate));
		pdo.name = c.getString(c.getColumnIndex(person_name));
		pdo.height = c.getInt(c.getColumnIndex(person_height));
		pdo.weight = c.getInt(c.getColumnIndex(person_weigth));
		pdo.level = c.getInt(c.getColumnIndex(person_level));
		return pdo;
	}
	
	// Dateformat: YYYY-MM-DD
	public StatisticDataObject getRepetitionsForExerciseForMonth(int persId,
			int year, int month, int exId) {
		StatisticDataObject sdo = new StatisticDataObject();
		SQLiteDatabase db = this.getReadableDatabase();

		String monthStr;
		if (month < 10) {
			monthStr = "0" + month;
		} else {
			monthStr = month + "";
		}

		String date_like_str = "'" + year + "-" + monthStr + "-%'"; // z.b.
																	// '2013-07-%'
		String qstr = "select sum(repetitions) as summe_rep, sum(accuracy) as summe_acc, sum(duration) as summe_dur from repcounter where "
				+ repC_persId
				+ " = "
				+ persId
				+ " AND "
				+ repC_date
				+ " LIKE "
				+ date_like_str + " AND " + repC_exerciseId + "=" + exId;
		Cursor c = db.rawQuery(qstr, null);

		Log.i("MALA", qstr);

		c.moveToNext();
		sdo.repetitions = c.getInt(c.getColumnIndex("summe_rep"));
		sdo.accuracy = c.getInt(c.getColumnIndex("summe_acc"));
		sdo.duration = c.getInt(c.getColumnIndex("summe_dur"));

		return sdo;

	}

	// OBSOLET
	// public HashMap<String, int[] > getRepetitions (int persId, String
	// startDate, String endDate) {
	// SQLiteDatabase db = this.getReadableDatabase();
	// // String [] columns=new String[]{colID, colPersId, colDate,
	// colExerciseId, colRepetitions};
	// //Cursor c=db.query(repCounterTable, columns, colPersId + "=? AND " +
	// colDate + ">=? AND " + colDate + "<=?", new
	// String[]{String.valueOf(persId), startDate, endDate}, null, null,null);
	// //Cursor c=db.query(repCounterTable, columns, colPersId + "=? AND " +
	// colDate + ">=? AND " + colDate + "<=?", new
	// String[]{String.valueOf(persId), startDate, endDate}, null, null,null);
	//
	// Cursor c=db.rawQuery("select * from repcounter where "+repC_persId+" = "
	// + persId + " AND " + repC_date + ">='" + startDate + "' AND " + repC_date
	// + "<='" + endDate+"'", null);
	//
	// HashMap<String, int[] > hm = new HashMap<String, int[]>();
	//
	// c.moveToFirst();
	//
	// while (!c.isAfterLast()) {
	// String date1 = c.getString(c.getColumnIndex(repC_date));
	// int rep = c.getInt(c.getColumnIndex(repC_repetitions));
	// int exId = c.getInt(c.getColumnIndex(repC_exerciseId));
	//
	// if (!hm.containsKey(date1)) {
	// int temp[] = new int[maxExerciseId_moveItElsewhere+1];
	// temp[exId] = rep;
	// hm.put(date1, temp);
	// } else {
	// hm.get(date1)[exId] = rep;
	// }
	// c.moveToNext();
	//
	// }
	// db.close();
	//
	// return hm;
	// }

	public void sendToWs(String tablename, String columns[]) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.rawQuery("select * from " + tablename, null);
	}

	
	
	// public HashMap<String, ArrayList<Integer> > getStatisticPerDay(int
	// persId, String startDate, String endDate) {
	// SQLiteDatabase db = this.getReadableDatabase();
	// HashMap<String, ArrayList<Integer> > = new HashMap<String,
	// ArrayList<Integer>>();
	//
	//
	// db.close();
	//
	// }
}